package com.saver.core.persistence;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.saver.core.domain.Transaccion;
import com.saver.core.persistence.table.TransaccionTable;
import com.saver.core.processor.util.Util;
import com.saver.core.util.Constantes;
import com.saver.core.util.ItemUtil;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class TransaccionSQLite extends DatabaseHelper {

    private static final String[] COLUMNAS = new String[]{TransaccionTable.COL_ID_TRANSACCION,
            TransaccionTable.COL_ID_BANCO, TransaccionTable.COL_ID_ESTABLECIMIENTO, TransaccionTable.COL_ID_CATEGORIA,
            TransaccionTable.COL_ID_MONEDA, TransaccionTable.COL_MONTO, TransaccionTable.COL_FECHA_SMS,
            TransaccionTable.COL_FECHA_TRANSACCION, TransaccionTable.COL_NUMERO, TransaccionTable.COL_REVISADO,
            TransaccionTable.COL_FECHA_TEXT, TransaccionTable.COL_ID_ESTABLECIMIENTOS_EQUIV,
            TransaccionTable.COL_ID_CATEGORIAS_EQUIV, TransaccionTable.COL_ID_MONEDA_EQUIV, TransaccionTable.COL_TIPO,
            TransaccionTable.COL_ID_SERVIDOR, TransaccionTable.COL_ESTADO, TransaccionTable.REFERENCIA};
    private static final String TOTALES_X_CATEGORIA = "select C.ID_CATEGORIA, C.NOMBRE," +
            "case when T.TOTAL is null then 0 else T.TOTAL end, C.ICONO , C.COLOR " +
            "from CATEGORIAS C LEFT JOIN " +
            "(select TT.ID_CATEGORIA, SUM(TT.MONTO) TOTAL  " +
            "from  tranSACCIONES TT   ";

    public TransaccionSQLite(Context context) {
        super(context, Constantes.DATABASE_NAME, null, Constantes.DATABASE_VERSION);
    }

    public void actualizarARevisado(Transaccion transaccion) {
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(TransaccionTable.COL_REVISADO, 1);
        db.update(TransaccionTable.TABLA, values, TransaccionTable.COL_ID_TRANSACCION + "=?", new String[]{transaccion.getId().toString()});
        db.close();
    }

    public void guardar(Transaccion transaccion) {
        ContentValues values = new ContentValues();
        values.put(TransaccionTable.COL_FECHA_SMS, transaccion.getFechaSMS());
        values.put(TransaccionTable.COL_FECHA_TEXT, Util.getStringToDateTime(new Date(transaccion.getFechaTransaccion())));
        values.put(TransaccionTable.COL_FECHA_TRANSACCION, transaccion.getFechaTransaccion());
        values.put(TransaccionTable.COL_ID_BANCO, transaccion.getIdBanco());
        values.put(TransaccionTable.COL_ID_CATEGORIA, transaccion.getIdCategoria());
        values.put(TransaccionTable.COL_ID_CATEGORIAS_EQUIV, transaccion.getIdCategoriaEquiv());
        values.put(TransaccionTable.COL_ID_ESTABLECIMIENTO, transaccion.getIdEstablecimiento());
        values.put(TransaccionTable.COL_ID_ESTABLECIMIENTOS_EQUIV, transaccion.getIdEstablecimientoEquiv());
        values.put(TransaccionTable.COL_ID_MONEDA, transaccion.getIdMoneda());
        values.put(TransaccionTable.COL_ID_MONEDA_EQUIV, transaccion.getIdMonedaEquiv());
        values.put(TransaccionTable.COL_ID_SERVIDOR, transaccion.getIdServidor());
        values.put(TransaccionTable.COL_MONTO, transaccion.getMonto());
        values.put(TransaccionTable.COL_NUMERO, transaccion.getNumero());
        values.put(TransaccionTable.COL_REVISADO, transaccion.getRevisado());
        values.put(TransaccionTable.COL_TIPO, transaccion.getTipo());
        values.put(TransaccionTable.COL_ESTADO, transaccion.getEstado());
        values.put(TransaccionTable.REFERENCIA, transaccion.getReferencia());

        SQLiteDatabase db = getWritableDatabase();
        int id = (int) db.insert(TransaccionTable.TABLA, null, values);
        transaccion.setId(id);
        db.close();
    }

    public void actualizar(Transaccion transaccion) {
        ContentValues values = new ContentValues();
        values.put(TransaccionTable.COL_FECHA_SMS, transaccion.getFechaSMS());
        values.put(TransaccionTable.COL_FECHA_TEXT, Util.getStringToDateTime(new Date(transaccion.getFechaTransaccion())));
        values.put(TransaccionTable.COL_FECHA_TRANSACCION, transaccion.getFechaTransaccion());
        values.put(TransaccionTable.COL_ID_BANCO, transaccion.getIdBanco());
        values.put(TransaccionTable.COL_ID_CATEGORIA, transaccion.getIdCategoria());
        values.put(TransaccionTable.COL_ID_CATEGORIAS_EQUIV, transaccion.getIdCategoriaEquiv());
        values.put(TransaccionTable.COL_ID_ESTABLECIMIENTO, transaccion.getIdEstablecimiento());
        values.put(TransaccionTable.COL_ID_ESTABLECIMIENTOS_EQUIV, transaccion.getIdEstablecimientoEquiv());
        values.put(TransaccionTable.COL_ID_MONEDA, transaccion.getIdMoneda());
        values.put(TransaccionTable.COL_ID_MONEDA_EQUIV, transaccion.getIdMonedaEquiv());
        values.put(TransaccionTable.COL_ID_SERVIDOR, transaccion.getIdServidor());
        values.put(TransaccionTable.COL_MONTO, transaccion.getMonto());
        values.put(TransaccionTable.COL_NUMERO, transaccion.getNumero());
        values.put(TransaccionTable.COL_REVISADO, transaccion.getRevisado());
        values.put(TransaccionTable.COL_TIPO, transaccion.getTipo());
        values.put(TransaccionTable.COL_ESTADO, transaccion.getEstado());
        values.put(TransaccionTable.REFERENCIA, transaccion.getReferencia());

        SQLiteDatabase db = getWritableDatabase();
        db.update(TransaccionTable.TABLA, values, TransaccionTable.COL_ID_TRANSACCION + "=?",
                new String[]{transaccion.getId().toString()});
        db.close();
    }

    public void eliminar(Transaccion transaccion) {
        SQLiteDatabase db = getWritableDatabase();
        db.delete(TransaccionTable.TABLA, TransaccionTable.COL_ID_TRANSACCION + "=?", new String[]{transaccion.getId().toString()});
        db.close();
    }

    public Transaccion buscarPorId(Integer id) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TransaccionTable.TABLA, COLUMNAS, TransaccionTable.COL_ID_TRANSACCION + "=?",
                new String[]{id.toString()}, null, null, null);
        Transaccion transaccion = new Transaccion();
        if (cursor.moveToNext()) {
            transaccion = crearTransaccion(cursor);
        }
        db.close();
        return transaccion;
    }

    public List<Transaccion> buscarTodo() {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TransaccionTable.TABLA, COLUMNAS, null, null, null, null, TransaccionTable.COL_FECHA_SMS + " DESC");
        ArrayList<Transaccion> transacciones = new ArrayList<Transaccion>();
        while (cursor.moveToNext()) {
            Transaccion transaccion = crearTransaccion(cursor);
            transacciones.add(transaccion);
        }
        db.close();
        return transacciones;
    }

    public int numeroTransacciones() {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.query(TransaccionTable.TABLA, new String[]{"COUNT(" + TransaccionTable.COL_ID_TRANSACCION + ")"},
                null, null, null, null, null);
        int numero = 0;
        if (cursor.moveToNext()) {
            numero = cursor.getInt(0);
            cursor.close();
        }
        db.close();
        return numero;
    }

    private Transaccion crearTransaccion(Cursor cursor) {
        Transaccion transaccion = new Transaccion();
        transaccion.setId(cursor.getInt(0));
        if (!cursor.isNull(1)) {
            transaccion.setIdBanco(cursor.getInt(1));
        }
        if (!cursor.isNull(2)) {
            transaccion.setIdEstablecimiento(cursor.getInt(2));
        }
        if (!cursor.isNull(3)) {
            transaccion.setIdCategoria(cursor.getInt(3));
        }
        if (!cursor.isNull(4)) {
            transaccion.setIdMoneda(cursor.getInt(4));
        }
        transaccion.setMonto(cursor.getDouble(5));
        transaccion.setFechaSMS(cursor.getLong(6));
        transaccion.setFechaTransaccion(cursor.getLong(7));
        transaccion.setNumero(cursor.getString(8));
        transaccion.setRevisado(cursor.getShort(9));
        transaccion.setFechaText(cursor.getString(10));
        if (!cursor.isNull(11)) {
            transaccion.setIdEstablecimientoEquiv(cursor.getInt(11));
        }
        if (!cursor.isNull(12)) {
            transaccion.setIdCategoriaEquiv(cursor.getInt(12));
        }
        if (!cursor.isNull(13)) {
            transaccion.setIdMonedaEquiv(cursor.getInt(13));
        }
        if (!cursor.isNull(14)) {
            transaccion.setTipo(cursor.getShort(14));
        }
        if (!cursor.isNull(15)) {
            transaccion.setIdServidor(cursor.getInt(15));
        }
        transaccion.setEstado(cursor.getShort(16));
        transaccion.setReferencia(cursor.getString(17));
        return transaccion;
    }

    public List<ItemUtil> listarTotalesXCategoria(int anio) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(TOTALES_X_CATEGORIA +
                " where strftime('%Y',fecha_text)='" +
                anio + "' GROUP BY TT.ID_CATEGORIA) T ON T.ID_CATEGORIA = C.ID_CATEGORIA ORDER BY T.TOTAL DESC, C.NOMBRE ASC;", null);
        ItemUtil element;
        List<ItemUtil> lista = new ArrayList<ItemUtil>();
        if (cursor.moveToFirst()) {
            do {
                element = new ItemUtil();
                element.setId(cursor.getInt(0));
                element.setDescripcion(cursor.getString(1));
                element.setValor(cursor.getFloat(2));
                element.setIcono(cursor.getString(3));
                element.setColor(cursor.getString(4));
                lista.add(element);
            } while (cursor.moveToNext());
            cursor.close();
        }
        return lista;
    }

    public List<ItemUtil> listarTotalesXCategoria(int anio, int mes) {
        DecimalFormat df = new DecimalFormat("00");
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(TOTALES_X_CATEGORIA +
                "where strftime('%Y',fecha_text)='" +
                anio +
                "' and strftime('%m',fecha_text) ='" +
                df.format(mes) +
                "' GROUP BY TT.ID_CATEGORIA) T ON T.ID_CATEGORIA = C.ID_CATEGORIA ORDER BY T.TOTAL DESC, C.NOMBRE ASC;", null);
        ItemUtil element;
        List<ItemUtil> lista = new ArrayList<ItemUtil>();
        if (cursor.moveToFirst()) {
            do {
                element = new ItemUtil();
                element.setId(cursor.getInt(0));
                element.setDescripcion(cursor.getString(1));
                element.setValor(cursor.getFloat(2));
                element.setIcono(cursor.getString(3));
                element.setColor(cursor.getString(4));
                lista.add(element);
            } while (cursor.moveToNext());
            cursor.close();
        }
        return lista;
    }

    public List<ItemUtil> listarTotalesXCategoria(int anio, int mes, int dia) {
        DecimalFormat df = new DecimalFormat("00");
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery(TOTALES_X_CATEGORIA +
                "where strftime('%Y',fecha_text)='" +
                anio +
                "' and strftime('%m',fecha_text) ='" +
                df.format(mes) +
                "' and strftime('%d',fecha_text) ='" +
                df.format(dia) +
                "' GROUP BY TT.ID_CATEGORIA) T ON T.ID_CATEGORIA = C.ID_CATEGORIA ORDER BY T.TOTAL DESC, C.NOMBRE ASC;", null);
        ItemUtil element;
        List<ItemUtil> lista = new ArrayList<ItemUtil>();
        if (cursor.moveToFirst()) {
            do {
                element = new ItemUtil();
                element.setId(cursor.getInt(0));
                element.setDescripcion(cursor.getString(1));
                element.setValor(cursor.getFloat(2));
                element.setIcono(cursor.getString(3));
                element.setColor(cursor.getString(4));
                lista.add(element);
            } while (cursor.moveToNext());
            cursor.close();
        }
        return lista;
    }

    public List<ItemUtil> listarTotalesXAnios() {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT strftime('%Y',fecha_text) label , sum(monto) " +
                "total FROM TRANSACCIONES group by strftime('%Y',fecha_text);", null);
        ItemUtil element;
        List<ItemUtil> lista = new ArrayList<ItemUtil>();
        if (cursor.moveToFirst()) {
            do {
                element = new ItemUtil();
                element.setDescripcion(cursor.getString(0));
                element.setValor(cursor.getFloat(1));
                lista.add(element);
            } while (cursor.moveToNext());
            cursor.close();
        }
        return lista;
    }

    public List<ItemUtil> listarTotalesXMeses(int anio) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT strftime('%m',fecha_text) label, sum(monto) total " +
                "FROM TRANSACCIONES " +
                "where strftime('%Y',fecha_text)='" +
                anio +
                "' group by strftime('%m',fecha_text);", null);
        ItemUtil element;
        List<ItemUtil> lista = new ArrayList<ItemUtil>();
        if (cursor.moveToFirst()) {
            do {
                element = new ItemUtil();
                element.setDescripcion(cursor.getString(0));
                element.setValor(cursor.getFloat(1));
                lista.add(element);
            } while (cursor.moveToNext());
            cursor.close();
        }
        return lista;
    }

    public List<ItemUtil> listarTotalesXDias(int anio, int mes) {
        DecimalFormat df = new DecimalFormat("00");
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT strftime('%d',fecha_text) label , sum(monto) total " +
                "FROM TRANSACCIONES " +
                "where strftime('%Y',fecha_text)='" +
                anio +
                "' and strftime('%m',fecha_text) ='" +
                df.format(mes) +
                "' group by strftime('%d',fecha_text);", null);
        ItemUtil element;
        List<ItemUtil> lista = new ArrayList<ItemUtil>();
        if (cursor.moveToFirst()) {
            do {
                element = new ItemUtil();
                element.setDescripcion(cursor.getString(0));
                element.setValor(cursor.getFloat(1));
                lista.add(element);
            } while (cursor.moveToNext());
            cursor.close();
        }
        return lista;
    }

    public Long maxFechaXBanco(int idBanco) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select max(" + TransaccionTable.COL_FECHA_SMS + ") from " +
                        TransaccionTable.TABLA + " where " + TransaccionTable.COL_ID_BANCO + " = ?",
                new String[]{String.valueOf(idBanco)});
        Long max = 0L;
        if (cursor.moveToNext()) {
            max = cursor.getLong(0);
            cursor.close();
        }
        return max;
    }

    public List<String> getTotalTabs(int tipoQuery, Integer categoria) {
        SQLiteDatabase db = getReadableDatabase();

        String where = "";
        if (categoria != null) {
            where = "WHERE ID_CATEGORIA = " + categoria;
        }

        Cursor cursor = null;
        switch (tipoQuery) {
            case Constantes.CONSULTA_X_ANIO:
                cursor = db.rawQuery("SELECT DISTINCT SUBSTR(FECHA_TEXT, 0, 5) FROM TRANSACCIONES " + where + " ORDER BY " + TransaccionTable.COL_FECHA_TRANSACCION + " ASC", null);
                break;
            case Constantes.CONSULTA_X_MES:
                cursor = db.rawQuery("SELECT DISTINCT SUBSTR(FECHA_TEXT, 0, 8) FROM TRANSACCIONES " + where + " ORDER BY " + TransaccionTable.COL_FECHA_TRANSACCION + " ASC", null);
                break;
            case Constantes.CONSULTA_X_DIA:
                cursor = db.rawQuery("SELECT DISTINCT FECHA_TEXT FROM TRANSACCIONES " + where + " ORDER BY " + TransaccionTable.COL_FECHA_TRANSACCION + " ASC", null);
                break;
        }
        List<String> tabs = new ArrayList<String>();
        if (cursor != null && cursor.moveToFirst()) {
            do {
                tabs.add(cursor.getString(0));
            } while (cursor.moveToNext());
            cursor.close();
        }
        return tabs;
    }
}
